﻿

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Gift.Core
{
    public sealed class SQLHelper
    {

        private SQLHelper()
        {

        }

        #region   ============Field==================
        static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ToString();

        #endregion

        #region ==========CommFunction==============


        /**/
        /// <summary>
        /// This method is used to attach array of SqlParameters to a SqlCommand.
        /// 
        /// This method will assign a value of DbNull to any parameter with a direction of
        /// InputOutput and a value of null.  
        /// 
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        /// </summary>
        /// <param name="command">The command to which the parameters will be added</param>
        /// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter p in commandParameters)
            {
                //check for derived output value with no value assigned
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }

                command.Parameters.Add(p);
            }
        }

        private static void PrepareCommand(SqlCommand sComm, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            //if the provided connection is not open, we will open it
            SqlConnection sConn = new SqlConnection(connStr);
            if (sConn.State != ConnectionState.Open)
            {

                sConn.Open();
            }


            //associate the connection with the command
            sComm.Connection = sConn;

            //set the command text (stored procedure name or SQL statement)
            sComm.CommandText = commandText;

            //if we were provided a transaction, assign it.
            if (transaction != null)
            {
                sComm.Transaction = transaction;
            }

            //set the command type
            sComm.CommandType = commandType;

            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(sComm, commandParameters);
            }

            return;
        }
        #endregion  =========END==============

        #region ===========DataSet===========
        ///<summary>
        ///
        ///</summary>
        public static DataSet GetDataSet(SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataAdapter sDa;

            DataSet sDs = new DataSet();

            PrepareCommand(sComm, transaction, commandType, commandText, commandParameters);

            //create the DataAdapter & DataSet
            sDa = new SqlDataAdapter(sComm);


            //fill the DataSet using default values for DataTable names, etc.
            sDa.Fill(sDs);

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();


            //return the dataset
            return sDs;
        }

        ///<summary>
        ///
        ///</summary>
        public static DataSet GetDataSet(CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataAdapter sDa;

            DataSet sDs = new DataSet();

            PrepareCommand(sComm, null, commandType, commandText, commandParameters);

            //create the DataAdapter & DataSet
            sDa = new SqlDataAdapter(sComm);
            sDs = new DataSet();

            //fill the DataSet using default values for DataTable names, etc.
            sDa.Fill(sDs);

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();


            //return the dataset
            return sDs;
        }

        ///<summary>
        ///
        ///</summary>
        public static DataSet GetDataSet(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataAdapter sDa;

            DataSet sDs = new DataSet();

            PrepareCommand(sComm, transaction, commandType, commandText, null);

            //create the DataAdapter & DataSet
            sDa = new SqlDataAdapter(sComm);
            sDs = new DataSet();

            //fill the DataSet using default values for DataTable names, etc.
            sDa.Fill(sDs);

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();


            //return the dataset
            return sDs;
        }

        ///<summary>
        ///
        ///</summary>
        public static DataSet GetDataSet(CommandType commandType, string commandText)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataAdapter sDa;

            DataSet sDs = new DataSet();

            PrepareCommand(sComm, null, commandType, commandText, null);

            //create the DataAdapter & DataSet

            sDa = new SqlDataAdapter(sComm);

            sDs = new DataSet();

            //fill the DataSet using default values for DataTable names, etc.
            sDa.Fill(sDs);

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();


            //return the dataset
            return sDs;
        }
        #endregion

        #region =============ExecuteSQLCommand=============

        public static int ExecuteSQL(SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters)
        {
            int exeCount = 0;
            SqlCommand sComm = new SqlCommand();
            PrepareCommand(sComm, transaction, commandType, commandText, commandParameters);

            exeCount = sComm.ExecuteNonQuery();

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();


            //return the execute result
            return exeCount;

        }

        public static int ExecuteSQL(CommandType commandType, String commandText, SqlParameter[] commandParameters)
        {
            int exeCount = 0;
            SqlCommand sComm = new SqlCommand();
            PrepareCommand(sComm, null, commandType, commandText, commandParameters);

            exeCount = sComm.ExecuteNonQuery();

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();

            //return the execute result
            return exeCount;

        }

        public static int ExecuteSQL(SqlTransaction transaction, CommandType commandType, String commandText)
        {
            int exeCount = 0;
            SqlCommand sComm = new SqlCommand();
            PrepareCommand(sComm, transaction, commandType, commandText, null);

            exeCount = sComm.ExecuteNonQuery();

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();

            //return the execute result
            return exeCount;

        }

        public static int ExecuteSQL(CommandType commandType, String commandText)
        {
            int exeCount = 0;
            SqlCommand sComm = new SqlCommand();
            PrepareCommand(sComm, null, commandType, commandText, null);

            exeCount = sComm.ExecuteNonQuery();

            // detach the SqlParameters from the command object, so they can be used again.            
            sComm.Parameters.Clear();

            //return the execute result
            return exeCount;

        }
        #endregion

        #region ===========ExecuteSQLDataReader================
        public static SqlDataReader ExecuteSQLReader(SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataReader sDr;
            PrepareCommand(sComm, transaction, commandType, commandText, commandParameters);
            sDr = sComm.ExecuteReader();

            return sDr;
        }

        public static  SqlDataReader  ExecuteSQLReader(CommandType commandType, String commandText, SqlParameter[] commandParameters)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataReader sDr;
            PrepareCommand(sComm, null, commandType, commandText, commandParameters);
            sDr = sComm.ExecuteReader();

            return sDr;
        }

        public static SqlDataReader ExecuteSQLReader(SqlTransaction transaction, CommandType commandType, String commandText)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataReader sDr;
            PrepareCommand(sComm, transaction, commandType, commandText, null);
            sDr = sComm.ExecuteReader();

            return sDr;
        }

        public static SqlDataReader ExecuteSQLReader(CommandType commandType, String commandText)
        {
            SqlCommand sComm = new SqlCommand();
            SqlDataReader sDr;
            PrepareCommand(sComm, null, commandType, commandText, null);
            sDr = sComm.ExecuteReader();

            return sDr;
        }
        #endregion============END=============

    }
}